<?php
/**
 * Database Analysis Script
 * Analyze all tables in the database to identify duplicates and invalid tables
 * 
 * Usage: php analyze_database.php
 */

// Load environment variables from .env file
$envFile = __DIR__ . '/../../.env';
if (file_exists($envFile)) {
    $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '=') !== false && strpos($line, '#') !== 0) {
            list($key, $value) = explode('=', $line, 2);
            $_ENV[trim($key)] = trim($value);
        }
    }
}

// Database configuration
try {
    $host = $_ENV['DB_HOST'] ?? '127.0.0.1';
    $port = $_ENV['DB_PORT'] ?? 3306;
    $dbname = $_ENV['DB_DATABASE'] ?? 'XappTool';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';
    
    echo "🔍 Analyzing database: $host:$port/$dbname\n";
    echo "Using username: $username\n\n";
    
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "✅ Database connection established successfully.\n\n";
    
    // Get all tables
    $stmt = $pdo->query("SHOW TABLES");
    $allTables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "📊 TOTAL TABLES FOUND: " . count($allTables) . "\n";
    echo "==================================================\n\n";
    
    // Categorize tables
    $gfTables = [];
    $nonGfTables = [];
    $potentialDuplicates = [];
    $systemTables = [];
    
    foreach ($allTables as $table) {
        if (strpos($table, 'gf_') === 0) {
            $gfTables[] = $table;
        } else {
            $nonGfTables[] = $table;
        }
        
        // Check for potential duplicates (similar names)
        $baseName = str_replace(['gf_', '_backup', '_old', '_test', '_temp'], '', $table);
        if (!isset($potentialDuplicates[$baseName])) {
            $potentialDuplicates[$baseName] = [];
        }
        $potentialDuplicates[$baseName][] = $table;
    }
    
    // Display analysis results
    echo "🟢 TABLES WITH gf_ PREFIX (" . count($gfTables) . "):\n";
    echo "--------------------------------------------------\n";
    foreach ($gfTables as $table) {
        echo "- $table\n";
    }
    echo "\n";
    
    if (!empty($nonGfTables)) {
        echo "🔴 TABLES WITHOUT gf_ PREFIX (" . count($nonGfTables) . "):\n";
        echo "--------------------------------------------------\n";
        foreach ($nonGfTables as $table) {
            echo "- $table\n";
        }
        echo "\n";
    }
    
    // Identify potential duplicates
    echo "⚠️  POTENTIAL DUPLICATE/VARIANT TABLES:\n";
    echo "--------------------------------------------------\n";
    $duplicateGroups = 0;
    foreach ($potentialDuplicates as $baseName => $variants) {
        if (count($variants) > 1) {
            echo "Base name: '$baseName'\n";
            foreach ($variants as $variant) {
                echo "  - $variant\n";
            }
            echo "\n";
            $duplicateGroups++;
        }
    }
    
    if ($duplicateGroups === 0) {
        echo "No potential duplicates found.\n\n";
    }
    
    // Generate recommendations
    echo "🎯 RECOMMENDATIONS:\n";
    echo "==================================================\n";
    
    if (!empty($nonGfTables)) {
        echo "1. RENAME these tables to use gf_ prefix:\n";
        foreach ($nonGfTables as $table) {
            echo "   - $table → gf_$table\n";
        }
        echo "\n";
    }
    
    if ($duplicateGroups > 0) {
        echo "2. REVIEW potential duplicate tables and remove unnecessary ones\n";
        echo "3. Keep the most recent/complete version of each table\n\n";
    }
    
    echo "4. GENERATE migration script for all changes\n";
    echo "5. EXPORT final SQL structure\n\n";
    
    // Save analysis results for next steps
    $analysisResults = [
        'all_tables' => $allTables,
        'gf_tables' => $gfTables,
        'non_gf_tables' => $nonGfTables,
        'potential_duplicates' => $potentialDuplicates,
        'timestamp' => date('Y-m-d H:i:s')
    ];
    
    file_put_contents('database_analysis_results.json', json_encode($analysisResults, JSON_PRETTY_PRINT));
    echo "📄 Analysis results saved to: database_analysis_results.json\n";
    
} catch (PDOException $e) {
    echo "❌ Database connection failed: " . $e->getMessage() . "\n";
    echo "\n💡 Troubleshooting tips:\n";
    echo "1. Check if MySQL is running\n";
    echo "2. Verify database credentials in your .env file\n";
    echo "3. Check if the database exists\n";
    echo "4. Ensure network connectivity to the database server\n";
    exit(1);
}

?>